package com.fjnldx.ks.mapper;

import com.fjnldx.ks.pojo.domain.*;
import com.fjnldx.ks.pojo.mesDoMain.*;
import com.fjnldx.ks.pojo.request.*;
import com.fjnldx.ks.pojo.response.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

@Component
@Mapper
public interface MesThirdMapper {

    /**
     * 查询实时并条数据
     */
    Map<String, Object> getNewestBingTiao(SchedulingShiftInModel  params);

    /**
     * 查询实时细纱马佐里数据
     */
    Map<String, Object> getNewestXiShaMzl(SchedulingShiftInModel params);

    /**
     * 查询分组实时细纱
     */
    List<DetailByPz> getGroupNewestXiShaMzl(SchedulingShiftInModel params);

    /**
     * 根据品种获取细纱车台范围
     *
     * @param pz
     * @return
     */
    @Select("SELECT devSn FROM mes_newscjh WHERE gongxu='细纱' AND DATEDIFF(time,now())=0 AND pz=#{pz} ORDER BY devSn+0 ASC")
    List<DevSnAreaList> getXiShaDevsnAreaByPz(String pz);

    /**
     * 根据品种获取粗纱车台范围
     *
     * @param pz
     * @return
     */
    @Select("SELECT devSn FROM mes_newscjh WHERE gongxu='粗纱' AND DATEDIFF(time,now())=0 AND pz=#{pz} ORDER BY devSn+0 ASC")
    List<DevSnAreaList> getCuShaDevsnAreaByPz(String pz);

    /**
     * 根据品种获取络筒车台范围
     *
     * @param pz
     * @return
     */
    @Select("SELECT devSn FROM mes_newscjh WHERE gongxu='络筒' AND DATEDIFF(time,now())=0 AND pz=#{pz} ORDER BY devSn+0 ASC")
    List<DevSnAreaList> getLuoTongDevsnAreaByPz(String pz);

    /**
     * 根据品种获取并条车台范围
     *
     * @param pz
     * @return
     */
    @Select("SELECT devSn FROM mes_newscjh WHERE gongxu='并条' AND devSn>100 AND DATEDIFF(time,now())=0 AND pz=#{pz} ORDER BY devSn+0 ASC")
    List<DevSnAreaList> getBingTiaoDevsnAreaByPz(String pz);

    /**
     * 根据品种获取梳棉车台范围
     *
     * @param pz
     * @return
     */
    @Select("SELECT devSn FROM mes_newscjh WHERE gongxu='梳棉' AND DATEDIFF(time,now())=0 AND pz=#{pz} ORDER BY devSn+0 ASC")
    List<DevSnAreaList> getShuMianDevsnAreaByPz(String pz);

    /**
     * 查询分组实时络筒
     *
     * @param xsReq
     * @return
     */
    List<DetailByPz> getGroupNewestLuoTong(XsReq xsReq);

    /**
     * 查询1.5级络筒用工数据
     *
     * @return
     */
    @Select("SELECT a.devSn,b.pz,a.xiaolv*100 xiaolv,ROUND(b.dh,3) dh,ROUND((a.zcd*b.dl)/1000,3) cl,ROUND(\n" +
            "( IF(HOUR(now())<12,TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now()),TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(60*60*12))/(12*60*60))*b.tc*3,3) AS jhcl,b.dcg,b.gh,b.gs,ROUND((a.zcd*b.dl*b.lzxs),3) gz FROM\n" +
            "(\n" +
            "SELECT a.devSn,a.zcd,b.xiaolv FROM\n" +
            "(\n" +
            "SELECT devSn,(bc1SUM+bc2SUM) zcd FROM spero_realtime_summary WHERE id IN (SELECT MAX(id) id FROM spero_realtime_summary WHERE DATEDIFF(`day`,now())=0 GROUP BY devSn)) a,\n" +
            "(\n" +
            "SELECT devSn,convert(runTime/totalTime,decimal(10,2))  xiaolv FROM spero_table WHERE id IN (SELECT MAX(id) id FROM spero_table WHERE DATEDIFF(time,now())=0 GROUP BY devSn)\n" +
            ") b WHERE a.devSn=b.devSn\n" +
            ") a,\n" +
            "(\n" +
            "SELECT a.*,b.lzxs FROM \n" +
            "(SELECT a.gh,a.`name` dcg,a.devSn,a.`share`,a.start_time,a.end_time,a.gs,b.dh,b.pz,b.dl,b.tc,b.zs FROM\n" +
            "(\n" +
            "SELECT a.gh,b.name,a.devSn,a.`share`,a.start_time,a.end_time,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),a.start_time))/60 AS gs,a.work_type\n" +
            "FROM\n" +
            "(SELECT current_work_type work_type, gh,devSn,`share`,start_time,end_time from scheduling where current_gongxu='络筒' AND current_work_type='挡车工' AND now()>=start_time AND now()<=end_time ORDER BY devSn+0 ASC) a\n" +
            "LEFT JOIN\n" +
            "(SELECT gh,`name`,work_type FROM scheduling_personnel) b\n" +
            "ON a.gh=b.gh\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT b.devSn,b.dh,a.pz,a.dl,a.tc*(a.xiaolv/100) tc,a.zs FROM mes_newscjh a,( SELECT devSn,MAX(ImpEp)-MIN(ImpEp) AS dh FROM dian_table\n" +
            "WHERE gongxu='自络' AND DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,HOUR(time)<12,HOUR(time)>=12) GROUP BY devSn) b WHERE a.gongxu='络筒' AND DATEDIFF(a.time,now())=0 AND a.devSn=b.devSn\n" +
            ") b\n" +
            "ON a.devSn=b.devSn) a\n" +
            "LEFT JOIN\n" +
            "(SELECT * FROM scheduling_labor_capital WHERE gongxu='络筒' AND work_type='公斤工资单价') b\n" +
            "ON \n" +
            "a.zs=b.zs \n" +
            "\n" +
            ") b\n" +
            "WHERE a.devSn=b.devSn ORDER BY a.devSn+0 ASC")
    List<GongXuYg> getYgLt();

    /**
     * 查询1.5级络筒用工数据汇总
     *
     * @return
     */
    List<UseWorkersDetailsSum> employmentSummaryLt();

    /**
     * 查询1.5级粗纱用工数据
     *
     * @return
     */
    @Select("SELECT a.*,b.gh,b.name dcg,ROUND(b.gs,3) gs,ROUND(b.gs*0.3,3) gz FROM\n" +
            "-- 产量品种\n" +
            "(SELECT a.devSn,ROUND((a.zcd*b.dl)/100,3) cl,ROUND(IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*b.tc,((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*b.tc),3) AS jhcl,xiaolv,pz FROM\n" +
            "(\n" +
            "-- 旧粗纱\n" +
            "SELECT devSn,MAX(zcl+0)-MIN(zcl+0) zcd FROM txcsoml_table WHERE DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn\n" +
            "UNION\n" +
            "-- 新粗纱\n" +
            "SELECT devSn,MAX(ljsc+0)-MIN(ljsc+0) zcd FROM txcsomlnew_table WHERE DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,dl,tc*(xiaolv/100) tc,xiaolv,pz FROM mes_newscjh WHERE DATEDIFF(time,now())=0 AND gongxu='粗纱' GROUP BY devSn) b\n" +
            "ON\n" +
            "a.devSn=b.devSn) a\n" +
            "LEFT JOIN\n" +
            "-- 用工\n" +
            "(SELECT a.gh,b.name,a.devSn,a.`share`,a.start_time,a.end_time,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),a.start_time))/60 AS gs,a.work_type\n" +
            "FROM\n" +
            "(SELECT current_work_type work_type, gh,devSn,`share`,start_time,end_time from scheduling where current_gongxu='粗纱' AND current_work_type='挡车工' AND now()>=start_time AND now()<=end_time ORDER BY devSn+0 ASC) a\n" +
            "LEFT JOIN\n" +
            "(SELECT gh,`name`,work_type FROM scheduling_personnel) b\n" +
            "ON a.gh=b.gh ) b\n" +
            "ON a.devSn=b.devSn ORDER BY a.devSn+0 ASC, b.name DESC\n")
    List<GongXuYg> getYgCs();

    /**
     * 查询1.5级粗纱用工数据-汇总
     *
     * @return
     */
    @Select("SELECT a.gh,a.dcg,a.pz,AVG(a.xiaolv) avg_xiaolv,ROUND(SUM(a.cl),3) sum_cl,ROUND(a.jhcl,3) sum_jhcl,ROUND(AVG(a.gs),3) gs,ROUND(SUM(a.gz),3) sum_gz FROM (\n" +
            "SELECT a.*,b.name dcg,ROUND(b.gs,3) gs,ROUND(b.gs*0.3,3) gz,b.gh FROM\n" +
            "-- 产量品种\n" +
            "(SELECT a.devSn,ROUND((a.zcd*b.dl)/100,3) cl,ROUND(IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*b.tc,((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*b.tc),3) AS jhcl,xiaolv,pz FROM\n" +
            "(\n" +
            "-- 旧粗纱\n" +
            "SELECT devSn,MAX(zcl+0)-MIN(zcl+0) zcd FROM txcsoml_table WHERE DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn\n" +
            "UNION\n" +
            "-- 新粗纱\n" +
            "SELECT devSn,MAX(ljsc+0)-MIN(ljsc+0) zcd FROM txcsomlnew_table WHERE DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,dl,tc*(xiaolv/100) tc,xiaolv,pz FROM mes_newscjh WHERE DATEDIFF(time,now())=0 AND gongxu='粗纱' GROUP BY devSn) b\n" +
            "ON\n" +
            "a.devSn=b.devSn) a\n" +
            "LEFT JOIN\n" +
            "-- 用工\n" +
            "(SELECT a.gh,b.name,a.devSn,a.`share`,a.start_time,a.end_time,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),a.start_time))/60 AS gs,a.work_type\n" +
            "FROM\n" +
            "(SELECT current_work_type work_type, gh,devSn,`share`,start_time,end_time from scheduling where current_gongxu='粗纱' AND current_work_type='挡车工' AND now()>=start_time AND now()<=end_time ORDER BY devSn+0 ASC) a\n" +
            "LEFT JOIN\n" +
            "(SELECT gh,`name`,work_type FROM scheduling_personnel) b\n" +
            "ON a.gh=b.gh ) b\n" +
            "ON a.devSn=b.devSn\n" +
            ") a GROUP BY a.dcg")
    List<UseWorkersDetailsSum> employmentSummaryCs();

    /**
     * 查询1.5级梳棉用工数据
     *
     * @return
     */
    @Select("SELECT a.devSn,a.gh,a.name dcg,a.gs,b.pz,b.dqllcl jhcl,b.sjzc cl,b.xiaolv,a.gs*0.3 gz FROM \n" +
            "(SELECT a.*,b.`name` FROM\n" +
            "(SELECT *,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),start_time))/60 AS gs FROM scheduling WHERE current_work_type='挡车工'\tAND current_gongxu='梳棉'\tAND now() BETWEEN start_time AND end_time\t) a\n" +
            "LEFT JOIN\n" +
            "(SELECT * FROM scheduling_personnel) b\n" +
            "ON a.gh=b.gh) a\n" +
            "LEFT JOIN\n" +
            "(SELECT a.devSn,a.pz,a.gongxu,IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*a.tc,((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*a.tc) AS dqllcl,ROUND((b.a_zcd*a.dl)/1000, 3) AS sjzc,a.xiaolv\n" +
            "FROM\n" +
            "(SELECT devSn,pz,gongxu,ds,dl,tc*(xiaolv/100) tc,xiaolv FROM mes_newscjh WHERE gongxu='梳棉' AND DATEDIFF(time,date(now()))=0) a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT devSn,MAX(zcd+0.0)-MIN(zcd+0.0) AS a_zcd FROM shumianji_table\n" +
            "WHERE DATEDIFF(time,date(now()))=0 AND (zcd REGEXP '[^0-9.]')=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn\n" +
            "UNION\n" +
            "SELECT devSn,MAX(zcd+0.0)-MIN(zcd+0.0) AS a_zcd FROM shumianjia_table\n" +
            "WHERE DATEDIFF(time,date(now()))=0 AND (zcd REGEXP '[^0-9.]')=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn\n" +
            ") b\n" +
            "ON a.devSn=b.devSn) b\n" +
            "ON a.devSn=b.devSn")
    List<GongXuYg> getYgSm();

    /**
     * 查询1.5级并条用工数据
     *
     * @return
     */
    @Select("SELECT a.name dcg,a.gh,a.gs,a.gs*0.3 gz,b.pz,a.devSn,b.xiaolv,ROUND(b.dqsjcl,3) cl,ROUND(b.dqllcl,3) jhcl FROM (\n" +
            "SELECT a.gh,b.name,a.devSn,a.`share`,a.start_time,a.end_time,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),a.start_time))/60 AS gs,a.work_type\n" +
            "FROM\n" +
            "(SELECT current_work_type work_type, gh,devSn,`share`,start_time,end_time from scheduling where current_gongxu='并条' AND current_work_type='挡车工' AND now()>=start_time AND now()<=end_time ORDER BY devSn+0 ASC) a\n" +
            "LEFT JOIN\n" +
            "(SELECT gh,`name`,work_type FROM scheduling_personnel) b\n" +
            "ON a.gh=b.gh\n" +
            ") a LEFT JOIN (\n" +
            "SELECT b.devSn,b.pz,b.gongxu,b.xiaolv,IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*b.tc,((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*b.tc) AS dqllcl,(a.a_zcd*b.dl*b.ds)/1000 AS dqsjcl FROM\n" +
            "(SELECT devSn,MAX(zcd+0.0)-MIN(zcd+0.0) AS a_zcd FROM bingtiao_table\n" +
            "WHERE devSn>100 AND DATEDIFF(time,date(now()))=0 AND (zcd REGEXP '[^0-9.]')=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn ORDER BY devSn+0 ASC) a\n" +
            "LEFT JOIN\n" +
            "(SELECT gongxu,devSn,dl,ds,pz,tc*(xiaolv/100) tc,xiaolv FROM mes_newscjh WHERE DATEDIFF(time,now())=0 AND gongxu='并条') b\n" +
            "ON a.devSn=b.devSn\n" +
            ") b\n" +
            "ON a.devSn=b.devSn")
    List<GongXuYg> getYgBt();

    /**
     * 查询1.5级梳棉用工数据-汇总
     *
     * @return
     */
    @Select("SELECT a.gh,a.name dcg,b.pz,AVG(b.xiaolv) avg_xiaolv,ROUND(SUM(b.sjzc),3) sum_cl,ROUND(SUM(b.dqllcl),3) sum_jhcl,ROUND(AVG(a.gs),3) gs,ROUND(a.gs*0.3,3) sum_gz FROM \n" +
            "(SELECT a.*,b.`name` FROM\n" +
            "(SELECT *,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),start_time))/60 AS gs FROM scheduling WHERE current_work_type='挡车工'\tAND current_gongxu='梳棉'\tAND now() BETWEEN start_time AND end_time\t) a\n" +
            "LEFT JOIN\n" +
            "(SELECT * FROM scheduling_personnel) b\n" +
            "ON a.gh=b.gh) a\n" +
            "LEFT JOIN\n" +
            "(SELECT a.devSn,a.pz,a.gongxu,IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*a.tc,((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*a.tc) AS dqllcl,ROUND((b.a_zcd*a.dl)/1000, 3) AS sjzc,a.xiaolv\n" +
            "FROM\n" +
            "(SELECT devSn,pz,gongxu,ds,dl,tc*(xiaolv/100) tc,xiaolv FROM mes_newscjh WHERE gongxu='梳棉' AND DATEDIFF(time,date(now()))=0) a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT devSn,MAX(zcd+0.0)-MIN(zcd+0.0) AS a_zcd FROM shumianji_table\n" +
            "WHERE DATEDIFF(time,date(now()))=0 AND (zcd REGEXP '[^0-9.]')=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn\n" +
            "UNION\n" +
            "SELECT devSn,MAX(zcd+0.0)-MIN(zcd+0.0) AS a_zcd FROM shumianjia_table\n" +
            "WHERE DATEDIFF(time,date(now()))=0 AND (zcd REGEXP '[^0-9.]')=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn\n" +
            ") b\n" +
            "ON a.devSn=b.devSn) b\n" +
            "ON a.devSn=b.devSn GROUP BY gh")
    List<UseWorkersDetailsSum> employmentSummarySm();

    /**
     * 查询1.5级并条用工数据-汇总
     *
     * @return
     */
    @Select("SELECT a.gh,a.dcg,a.pz,AVG(a.xiaolv) avg_xiaolv,ROUND(SUM(a.cl),3) sum_cl,ROUND(SUM(a.jhcl),3) sum_jhcl,ROUND(AVG(a.gs),3) gs,ROUND(a.gs*0.3,3) sum_gz FROM (\n" +
            "SELECT a.name dcg,a.gh,a.gs,a.gs*0.3 gz,b.pz,a.devSn,b.xiaolv,ROUND(b.dqsjcl,3) cl,ROUND(b.dqllcl,3) jhcl FROM (\n" +
            "SELECT a.gh,b.name,a.devSn,a.`share`,a.start_time,a.end_time,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),a.start_time))/60 AS gs,a.work_type\n" +
            "FROM\n" +
            "(SELECT current_work_type work_type, gh,devSn,`share`,start_time,end_time from scheduling where current_gongxu='并条' AND current_work_type='挡车工' AND now()>=start_time AND now()<=end_time ORDER BY devSn+0 ASC) a\n" +
            "LEFT JOIN\n" +
            "(SELECT gh,`name`,work_type FROM scheduling_personnel) b\n" +
            "ON a.gh=b.gh\n" +
            ") a LEFT JOIN (\n" +
            "SELECT b.devSn,b.pz,b.gongxu,b.xiaolv,IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*b.tc,((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*b.tc) AS dqllcl,(a.a_zcd*b.dl*b.ds)/1000 AS dqsjcl FROM\n" +
            "(SELECT devSn,MAX(zcd+0.0)-MIN(zcd+0.0) AS a_zcd FROM bingtiao_table\n" +
            "WHERE devSn>100 AND DATEDIFF(time,date(now()))=0 AND (zcd REGEXP '[^0-9.]')=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn ORDER BY devSn+0 ASC) a\n" +
            "LEFT JOIN\n" +
            "(SELECT gongxu,devSn,dl,ds,pz,tc*(xiaolv/100) tc,xiaolv FROM mes_newscjh WHERE DATEDIFF(time,now())=0 AND gongxu='并条') b\n" +
            "ON a.devSn=b.devSn\n" +
            ") b\n" +
            "ON a.devSn=b.devSn) a GROUP BY a.gh ORDER BY devSn+0 ASC")
    List<UseWorkersDetailsSum> employmentSummaryBt();

    /**
     * 查询分组实时并条
     *
     * @param xsReq
     * @return
     */
    @Select("SELECT a.pz,AVG(a.xiaolv) xiaolvavg,ROUND(SUM(a.dqllcl),3) jhclsum,ROUND(SUM(a.dqsjcl),3) sjclsum,ROUND(SUM(b.dh),3) dh,ROUND(((SUM(b.dh)*1000)/SUM(a.dqsjcl)), 3) AS dshd FROM\n" +
            "(\n" +
            "SELECT b.devSn,b.pz,b.gongxu,b.xiaolv,IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*b.tc,((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*b.tc) AS dqllcl,(a.a_zcd*b.dl*b.ds)/1000 AS dqsjcl FROM\n" +
            "(SELECT devSn,MAX(zcd+0.0)-MIN(zcd+0.0) AS a_zcd FROM bingtiao_table\n" +
            "WHERE devSn>100 AND DATEDIFF(time,date(now()))=0 AND (zcd REGEXP '[^0-9.]')=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn ORDER BY devSn+0 ASC) a\n" +
            "LEFT JOIN\n" +
            "(SELECT gongxu,devSn,dl,ds,pz,tc*(xiaolv/100) tc,xiaolv FROM mes_newscjh WHERE DATEDIFF(time,now())=0 AND gongxu='并条') b\n" +
            "ON a.devSn=b.devSn\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT a.devSn,SUM(a.dh) dh FROM (\n" +
            "SELECT IF(devSn<100,devSn+100,devSn-900) devSn,MAX(ImpEp)-MIN(ImpEp) AS dh FROM dian_table\n" +
            "WHERE gongxu='并条' AND DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn ) a GROUP BY devSn) b\n" +
            "ON a.devSn=b.devSn GROUP BY pz")
    List<DetailByPz> getGroupNewestBingTiao(XsReq xsReq);

    /**
     * 查询分组实时粗纱
     *
     * @param xsReq
     * @return
     */
    @Select("SELECT a.pz,AVG(a.xiaolv) xiaolvavg,SUM(a.dqsjcl) sjclsum,SUM(a.dqllcl) jhclsum,ROUND(SUM(a.dh),3) dh,ROUND(((SUM(a.dh)*1000)/SUM(a.dqsjcl)), 3) dshd FROM\n" +
            "(SELECT a.*,b.dh FROM\n" +
            "-- 产量\n" +
            "(SELECT a.devSn,ROUND((a.zcd*b.dl)/100,3) dqsjcl,ROUND(IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*b.tc,((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*b.tc),3) AS dqllcl,b.tc,xiaolv,pz FROM\n" +
            "(\n" +
            "-- 旧粗纱\n" +
            "SELECT devSn,MAX(zcl+0)-MIN(zcl+0) zcd FROM txcsoml_table WHERE DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn\n" +
            "UNION\n" +
            "-- 新粗纱\n" +
            "SELECT devSn,MAX(ljsc+0)-MIN(ljsc+0) zcd FROM txcsomlnew_table WHERE DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,dl,tc*(xiaolv/100) tc,xiaolv,pz FROM mes_newscjh WHERE DATEDIFF(time,now())=0 AND gongxu='粗纱' GROUP BY devSn) b\n" +
            "ON\n" +
            "a.devSn=b.devSn) a\n" +
            "LEFT JOIN\n" +
            "-- 电量\n" +
            "(SELECT devSn,MAX(ImpEp)-MIN(ImpEp) AS dh FROM dian_table\n" +
            "WHERE gongxu='粗纱' AND DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn) b\n" +
            "ON a.devSn=b.devSn) a GROUP BY a.pz")
    List<DetailByPz> getGroupNewestCusha(XsReq xsReq);

    /**
     * 查询分组实时梳棉+梳棉a
     *
     * @param xsReq
     * @return
     */
    @Select("SELECT a.gongxu,a.pz,ROUND(sum(a.sjzc), 3) AS sjclsum,ROUND(sum(a.dqllcl), 3) AS jhclsum,ROUND(avg(a.xiaolv),3) xiaolvavg,ROUND(SUM(a.dh),3) dh FROM(\n" +
            "SELECT a.tc,a.devSn,a.pz,a.gongxu,a.ds,a.dl,IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*a.tc,((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*a.tc) AS dqllcl,ROUND((b.a_zcd*a.dl)/1000, 3) AS sjzc,a.xiaolv,a.dh\n" +
            "FROM\n" +
            "(SELECT a.devSn,a.pz,a.gongxu,a.ds,a.dl,a.tc*(a.xiaolv/100) tc,a.xiaolv,b.dh FROM mes_newscjh a,(SELECT devSn,MAX(ImpEp)-MIN(ImpEp) AS dh FROM dian_table WHERE gongxu='梳棉' AND DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn) b WHERE a.devSn=b.devSn AND gongxu='梳棉' AND DATEDIFF(time,date(now()))=0)a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT devSn,MAX(zcd+0.0)-MIN(zcd+0.0) AS a_zcd FROM shumianji_table\n" +
            "WHERE DATEDIFF(time,date(now()))=0 AND (zcd REGEXP '[^0-9.]')=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn\n" +
            "UNION\n" +
            "SELECT devSn,MAX(zcd+0.0)-MIN(zcd+0.0) AS a_zcd FROM shumianjia_table\n" +
            "WHERE DATEDIFF(time,date(now()))=0 AND (zcd REGEXP '[^0-9.]')=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn\n" +
            ") b\n" +
            "ON a.devSn=b.devSn\n" +
            ") a GROUP BY pz")
    List<DetailByPz> getGroupNewestShuMian(XsReq xsReq);

    /**
     * 查询实时太行粗纱欧姆龙数据
     * @param devSn
     * @return
     */
    @Select("SELECT a.*,b.cl dqsjcl,b.jhcl dqllcl,b.xiaolv,b.pz,b.dcg,ROUND(b.s_zcd,3) s_zcd FROM\n" +
            "(select *,conv(fsdc,10,16)-conv(ylsc,10,16) sycd  from txcsoml_table where devSn=#{devSn} ORDER BY id DESC limit 0,1) a\n" +
            "LEFT JOIN\n" +
            "(SELECT a.*,b.dcg FROM\n" +
            "-- 产量品种\n" +
            "(SELECT a.devSn,a.zcd s_zcd,ROUND((a.zcd*b.dl)/100,3) cl,ROUND(IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*b.tc,((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*b.tc),3) AS jhcl,xiaolv,pz FROM\n" +
            "(\n" +
            "-- 旧粗纱\n" +
            "SELECT devSn,MAX(zcl+0)-MIN(zcl+0) zcd FROM txcsoml_table WHERE DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,dl,tc,xiaolv,pz FROM mes_newscjh WHERE DATEDIFF(time,now())=0 AND gongxu='粗纱' GROUP BY devSn) b\n" +
            "ON\n" +
            "a.devSn=b.devSn) a\n" +
            "LEFT JOIN\n" +
            "-- 用工\n" +
            "(SELECT a.gh,GROUP_CONCAT(a.gh,' ',b.name SEPARATOR ',') dcg,a.devSn,a.`share`,a.start_time,a.end_time,a.work_type\n" +
            "FROM\n" +
            "(SELECT current_work_type work_type, gh,devSn,`share`,start_time,end_time from scheduling where current_gongxu='粗纱' AND current_work_type='挡车工' AND now()>=start_time AND now()<=end_time ORDER BY devSn+0 ASC) a\n" +
            "LEFT JOIN\n" +
            "(SELECT gh,`name`,work_type FROM scheduling_personnel) b\n" +
            "ON a.gh=b.gh GROUP BY devSn) b\n" +
            "ON a.devSn=b.devSn WHERE a.devSn=#{devSn}) b\n" +
            "ON a.devSn=b.devSn\n")
    Map<String, Object> getNewestTxcsoml(String devSn);

    /**
     * 查询实时太行粗纱欧姆龙数据-新设备
     *
     * @param devSn
     * @return
     */
    @Select("SELECT a.*,b.cl dqsjcl,b.jhcl dqllcl,b.xiaolv,b.pz pz2,b.dcg,ROUND(b.s_zcd,3) s_zcd FROM\n" +
            "(SELECT *,(fsdc-ylsc) sycd FROM txcsomlnew_table where devSn=#{devSn} ORDER BY id DESC limit 0,1) a\n" +
            "LEFT JOIN\n" +
            "(SELECT a.*,b.dcg FROM\n" +
            "-- 产量品种\n" +
            "(SELECT a.devSn,a.zcd s_zcd,ROUND((a.zcd*b.dl)/100,3) cl,ROUND(IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*b.tc,((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*b.tc),3) AS jhcl,xiaolv,pz FROM\n" +
            "(\n" +
            "-- 新粗纱\n" +
            "SELECT devSn,MAX(ljsc+0)-MIN(ljsc+0) zcd FROM txcsomlnew_table WHERE DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,dl,tc,xiaolv,pz FROM mes_newscjh WHERE DATEDIFF(time,now())=0 AND gongxu='粗纱' GROUP BY devSn) b\n" +
            "ON\n" +
            "a.devSn=b.devSn) a\n" +
            "LEFT JOIN\n" +
            "-- 用工\n" +
            "(SELECT a.gh,GROUP_CONCAT(a.gh,' ',b.name SEPARATOR ',') dcg,a.devSn,a.`share`,a.start_time,a.end_time,a.work_type\n" +
            "FROM\n" +
            "(SELECT current_work_type work_type, gh,devSn,`share`,start_time,end_time from scheduling where current_gongxu='粗纱' AND current_work_type='挡车工' AND now()>=start_time AND now()<=end_time ORDER BY devSn+0 ASC) a\n" +
            "LEFT JOIN\n" +
            "(SELECT gh,`name`,work_type FROM scheduling_personnel) b\n" +
            "ON a.gh=b.gh GROUP BY devSn) b\n" +
            "ON a.devSn=b.devSn WHERE a.devSn=#{devSn}) b\n" +
            "ON a.devSn=b.devSn")
    Map<String, Object> getNewestTxcsomlNew(String devSn);

    /**
     * 查询实时太行粗纱欧姆龙数据-新设备-甲班
     *
     * @param devSn
     * @return
     */
    @Select("SELECT a.*,b.pz,ROUND((a.ljsc),3) AS dqbcl,ROUND((a.ljsc)*b.ds*b.dl,3) AS zlbcl FROM\n" +
            "(\n" +
            "SELECT time,stateCode,devSn,llsjzs,llxsd,dysjzs,ylsc,fsdc,ljsc,jllssj,ROUND((dysjzs / llxsd),3) AS csnd,ljsjxsd,ljsjwz,ROUND((fsdc-ylsc),3) AS sycd FROM txcsomlnew_table WHERE DATEDIFF(time,now())=0 AND devSn=#{devSn} ORDER BY id DESC LIMIT 0,1\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT pz,ds,dl,devSn FROM mes_newscjh WHERE DATEDIFF(time,now())=0 AND gongxu='粗纱' AND devSn=#{devSn} \n" +
            ") b\n" +
            "ON a.devSn=b.devSn")
    NewTxcsoml getNewestTxcsomlNewJ(String devSn);

    /**
     * 查询实时梳棉机1207数据
     *
     * @param devSn
     * @return
     */
    @Select("SELECT a.* ,b.zbllcl,b.pz,b.gongxu,b.dqllcl,b.sjzc,b.dcg,b.dbcd a_zcd FROM\n" +
            "(select * from shumianji_table where devSn=#{devSn} ORDER BY id DESC limit 0,1) a,\n" +
            "(SELECT a.*,b.dcg FROM\n" +
            "(SELECT a.tc/2 zbllcl,a.devSn,a.pz,a.gongxu,IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*a.tc,((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*a.tc) AS dqllcl,ROUND((b.a_zcd*a.dl)/1000, 3) AS sjzc,b.a_zcd dbcd\n" +
            "FROM\n" +
            "(SELECT devSn,pz,gongxu,ds,dl,tc FROM mes_newscjh WHERE gongxu='梳棉' AND DATEDIFF(time,date(now()))=0) a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT devSn,MAX(zcd+0.0)-MIN(zcd+0.0) AS a_zcd FROM shumianji_table\n" +
            "WHERE DATEDIFF(time,date(now()))=0 AND (zcd REGEXP '[^0-9.]')=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) AND devSn=#{devSn} GROUP BY devSn\n" +
            ") b\n" +
            "ON a.devSn=b.devSn) a\n" +
            "LEFT JOIN\n" +
            "(" +
            "SELECT a.devSn,GROUP_CONCAT(a.gh,b.`name` SEPARATOR \",\") dcg FROM\n" +
            "(SELECT devSn,gh FROM scheduling  WHERE current_gongxu='梳棉' AND NOW() BETWEEN start_time AND end_time AND devSn=#{devSn} GROUP BY devSn) a\n" +
            "LEFT JOIN\n" +
            "scheduling_personnel b\n" +
            "ON\n" +
            "a.gh=b.gh GROUP BY devSn" +
            ") b\n" +
            "ON a.devSn=b.devSn) b\n" +
            "WHERE a.devSn=b.devSn")
    Map<String, Object> getNewestShuMianJi(String devSn);

    /**
     * 查询实时梳棉机1207A数据
     *
     * @param devSn
     * @return
     */
    @Select("SELECT a.* ,b.zbllcl,b.pz,b.gongxu,b.dqllcl,b.sjzc,b.dcg,b.dbcd a_zcd FROM\n" +
            "(select * from shumianjia_table where devSn=#{devSn} ORDER BY id DESC limit 0,1) a,\n" +
            "(SELECT a.*,b.dcg FROM\n" +
            "(SELECT a.tc/2 zbllcl,a.devSn,a.pz,a.gongxu,IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*a.tc,((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*a.tc) AS dqllcl,ROUND((b.a_zcd*a.dl)/1000, 3) AS sjzc,b.a_zcd dbcd\n" +
            "FROM\n" +
            "(SELECT devSn,pz,gongxu,ds,dl,tc FROM mes_newscjh WHERE gongxu='梳棉' AND DATEDIFF(time,date(now()))=0) a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT devSn,MAX(zcd+0.0)-MIN(zcd+0.0) AS a_zcd FROM shumianjia_table\n" +
            "WHERE DATEDIFF(time,date(now()))=0 AND (zcd REGEXP '[^0-9.]')=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) AND devSn=#{devSn} GROUP BY devSn\n" +
            ") b\n" +
            "ON a.devSn=b.devSn) a\n" +
            "LEFT JOIN\n" +
            "(SELECT a.devSn,GROUP_CONCAT(a.gh,b.`name` SEPARATOR \",\") dcg FROM scheduling a,scheduling_personnel b WHERE a.gh=b.gh AND a.current_gongxu='梳棉' AND NOW() BETWEEN a.start_time AND a.end_time AND devSn=#{devSn} GROUP BY devSn) b\n" +
            "ON a.devSn=b.devSn) b\n" +
            "WHERE a.devSn=b.devSn")
    Map<String, Object> getNewestShumianjiaTable(String devSn);

    /**
     * 查询实时多仓数据
     *
     * @param devSn
     * @return
     */
    @Select("select * from duocan_table where devSn=#{devSn} ORDER BY id DESC limit 0,1")
    Map<String,Object> getNewestDuoCanTable(String devSn);

    /**
     * 查询实时精开棉机数据
     *
     * @param devSn
     * @return
     */
    @Select("select * from jingkaimianji_table where devSn=#{devSn} ORDER BY id DESC limit 0,1")
    Map<String,Object> getNewestJingkaimianjiTable(String devSn);

    /**
     * 查询实时抓棉机数据
     *
     * @param devSn
     * @return
     */
    @Select("select * from zmj_table where devSn=#{devSn} ORDER BY id DESC limit 0,1")
    Map<String,Object> getNewestZmjTable(String devSn);

    /**
     * 查询实时络筒数据
     *
     * @param devSn
     * @return
     */
    SperoTable getNewestLuoTongTable(SchedulingShiftInModel params);
    SperoTable getLastLt(SchedulingShiftInModel params);
    SperoTable getEarlyLt(SchedulingShiftInModel params);
    List<Map<String,Object>> querySpindleNumberMapping(SchedulingShiftInModel params);
    /**
     * 分组数据
     */
    /**
     * 细纱设备分组数据
     *
     * @return
     */
    List<EquipmentFloatingWindow> getGroupXishaArr1();

    /**
     * 太行粗纱欧姆龙分组数据：新+旧
     *
     * @return
     */
    List<EquipmentFloatingWindow> getGroupTxcsomlArr1();

    /**
     * 并条分组数据
     *
     * @return
     */
    List<EquipmentFloatingWindow> getGroupBingTiaoArr1();

    /**
     * 梳棉机分组数据
     *
     * @return
     */
    List<EquipmentFloatingWindow> getGroupShuMianJiArr1();
    List<EquipmentFloatingWindow> getGroupShuMianJiAArr2();

    /**
     * 查询多仓分组数据
     *
     * @return
     */
    List<EquipmentFloatingWindow> getGroupDuoCanArr();

    /**
     * 查询精开棉机分组数据
     *
     * @return
     */
    List<EquipmentFloatingWindow> getGroupJingKaiMianJiArr();

    /**
     * 查询抓棉机分组数据
     *
     * @return
     */
    List<EquipmentFloatingWindow> getGroupZmjArr();

    /**
     * 查询络筒分组数据
     *
     * @return
     */
    List<EquipmentFloatingWindow> getGroupLuoTongArr();

    /**
     * 查询电表分组数据
     *
     * @return
     */
    @Select("SELECT * FROM(SELECT *,ROW_NUMBER() OVER(PARTITION BY gongxu ORDER BY time DESC) AS Row_Sort FROM dian_table ) a WHERE a.Row_Sort=1 ")
    List<DianBiao> getGroupDianBiaoArr();

    /**
     * 查询动力柜数据
     *
     * @param dlg
     * @return
     */
    @Select("<script> " +
            "SELECT a.*, b.pz,b.dqbc,b.dqbc_zcd,b.IavgLl,ROUND((a.AvgI-b.IavgLl)*100/b.IavgLl,3) dlpc FROM ( " +
            "select dt.devSn, dt.gongxu,dt.dthd, dt.zhd, db.dbhd,dy.dyhd, dz.dzhd ,pj.AvgU, pj.AvgI from " +
            "(select devSn, gongxu ,max(ImpEp) as zhd ,max(ImpEp) - min(ImpEp) as dthd from dian_table " +
            " where gongxu=#{gongxu} and devSn BETWEEN #{start}  and #{end} and  time >DATE_FORMAT( NOW(), '%Y-%m-%d 00:00:00' )   " +
            "GROUP BY devSn ) dt " +
            " left join" +
            " (select devSn, max(ImpEp) - min(ImpEp) as dbhd from dian_table " +
            " WHERE gongxu=#{gongxu} and devSn BETWEEN #{start}  and #{end} and time >IF(DATE_FORMAT(NOW(),'%Y-%m-%d 12:00:00' )>now(),DATE_FORMAT( NOW(),'%Y-%m-%d 00:00:00'),DATE_FORMAT(NOW(),'%Y-%m-%d 12:00:00') )" +
            " GROUP BY devSn  ) db on dt.devSn=db.devSn " +
            " left join " +
            "(select devSn, sum(haodian) as dyhd from dian_day_summary " +
            "where gongxu=#{gongxu} and devSn BETWEEN #{start}  and #{end} and  day >DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00')  " +
            "GROUP BY devSn ) dy on dy.devSn = dt.devSn " +
            " left join " +
            "(select devSn, sum(haodian) as dzhd from dian_day_summary " +
            "where gongxu=#{gongxu} and devSn BETWEEN #{start}  and #{end} and  day > DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00') GROUP BY devSn) dz on dz.devSn = dt.devSn" +
            " left join " +
            "(select z.devSn," +
            "<if test='gongxu == \"滤尘\"'>" +
            " Uavg as AvgU, Iavg as AvgI " +
            "</if>" +
            "<if test='gongxu != \"滤尘\"'>" +
            "ROUND((z.Ua+z.Ub+z.Uc)/3,3 ) as AvgU,ROUND((z.Ia+z.Ib+z.Ic)/3,3 ) as AvgI" +
            "</if>" +
            " from " +
            "dian_table z  join (select devSn , max(time) as time from dian_table " +
            "where gongxu=#{gongxu} and devSn BETWEEN #{start}  and #{end}  GROUP  BY devSn ) d on z.devSn = d.devSn and z.time = d.time ) pj on dt.devSn = pj.devSn " +
            ") a " +
            "LEFT JOIN\n" +
            "(" +
            "SELECT a.*,b.ROI,ROUND(a.llxs/b.ROI,3) IavgLl FROM (\n" +
            "SELECT  a.devSn,a.pz,ROUND((b.a_zcd*a.dl*a.ds)/100000, 3) AS dqbc,b.a_zcd AS dqbc_zcd,b.dqcd,b.llxs FROM \n" +
            "(SELECT devSn,pz,gongxu,ds,dl,tc FROM mes_newscjh WHERE gongxu='细纱' AND DATEDIFF(time,now())=0) a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT a.*,b.dqcd,b.llxs FROM\n" +
            "(SELECT devSn,MAX(id) id,MAX(zcd+0.0)-MIN(zcd+0.0) AS a_zcd FROM xsmzl_table\n" +
            "WHERE DATEDIFF(time,date(now()))=0 AND IF(HOUR(now())&lt;=12, extract(hour from time) &lt;=12, extract(hour from time) &gt;=12)\n" +
            "GROUP BY devSn) a\n" +
            "LEFT JOIN\n" +
            "xsmzl_table b ON a.id=b.id\n" +
            ") b  \n" +
            "ON a.devSn=b.devSn\n" +
            ")a\n" +
            "LEFT JOIN\n" +
            "analysis b\n" +
            "ON FLOOR(a.dqcd)=b.dqcd AND a.pz=b.pz" +
            ") b\n" +
            "ON a.devSn+''=b.devSn" +
            "</script>"
    )
    List<Dlg> getDlg(DlgReq dlg);

    List<YieldOfEachVariety> queryXsYieldVariety(SchedulingShiftInModel params);

    /**
     * 车间开台比例-圆环
     */
    @Select("SELECT a.pz,a.devSnNum,b.dqsjcl FROM\n" +
            "(\n" +
            "SELECT pz,count(devSn) AS devSnNum FROM mes_newscjh WHERE gongxu='细纱' AND DATEDIFF(time,now())=0 GROUP BY pz\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT a.pz,ROUND(sum(a.sjzc), 3) AS dqsjcl,ROUND(sum(a.tc), 3) AS dqllcl,ROUND(sum(a.tc)/2, 3) AS zbllcl  FROM\n" +
            "(SELECT a.devSn,a.pz,a.gongxu,a.ds,a.dl,a.tc,b.zcd,ROUND((b.zcd*a.dl*a.ds)/100000, 3) AS sjzc\n" +
            "FROM \n" +
            "(\n" +
            "SELECT devSn,pz,gongxu,ds,dl,tc FROM mes_newscjh WHERE gongxu='细纱' AND DATEDIFF(time,NOW())=0\n" +
            ") AS a \n" +
            "LEFT JOIN \n" +
            "(\n" +
            "SELECT devSn,MAX(zcd+0.0)-MIN(zcd+0.0) AS zcd FROM xsmzl_table WHERE DATEDIFF(time,now())=0 GROUP BY devSn ORDER BY devSn+0 ASC\n" +
            ") b\n" +
            "ON \n" +
            "a.devSn=b.devSn AND a.gongxu='细纱') a\n" +
            "GROUP BY a.pz\n" +
            ") b\n" +
            "ON a.pz=b.pz")
    List<WorkshopOpeningRatioRing> workshopOpeningRatioRing();

    /**
     * 车间开台比例-条形图
     *
     * @return
     */
    @Select("SELECT gongxu,COUNT(*) AS jhkt FROM mes_newscjh WHERE DATEDIFF(time,now())=0 GROUP BY gongxu")
    List<JhKt> workshopOpeningRatioBarChart();

    /**
     * 车间开台比例-车间总台数
     *
     * @return
     */
    List<WorkshopOpening> workshopAllDevSnOfGx();

    /**
     * 车间开台比例-条形图-实际运行
     *
     * @return
     */
    @Select("SELECT '细纱' AS gongxu,COUNT(*) AS sjyx  FROM (\n" +
            "SELECT a.devSn,b.stateCode FROM\n" +
            "(SELECT devSn,MAX(id) AS id FROM xsmzl_table GROUP BY devSn ) a\n" +
            "LEFT JOIN\n" +
            "(SELECT id,stateCode FROM xsmzl_table WHERE DATEDIFF(time,now())=0) b\n" +
            "ON a.id=b.id GROUP BY devSn ) a\n" +
            "WHERE a.stateCode='1'\n" +
            "UNION\n" +
            "SELECT '粗纱' AS gongxu,COUNT(*) AS sjyx  FROM (\n" +
            "SELECT a.devSn,b.stateCode FROM\n" +
            "(SELECT devSn,MAX(id) AS id FROM txcsoml_table GROUP BY devSn ) a\n" +
            "LEFT JOIN\n" +
            "(SELECT id,stateCode FROM txcsoml_table WHERE DATEDIFF(time,now())=0) b\n" +
            "ON a.id=b.id GROUP BY devSn \n" +
            "UNION\n" +
            "SELECT a.devSn,b.stateCode FROM\n" +
            "(SELECT devSn,MAX(id) AS id FROM txcsomlnew_table GROUP BY devSn ) a\n" +
            "LEFT JOIN\n" +
            "(SELECT id,stateCode FROM txcsomlnew_table WHERE DATEDIFF(time,now())=0) b\n" +
            "ON a.id=b.id GROUP BY devSn \n" +
            ") a\n" +
            "WHERE a.stateCode='1'\n" +
            "UNION\n" +
            "SELECT '并条' AS gongxu,COUNT(*) AS sjyx  FROM (\n" +
            "SELECT a.devSn,b.stateCode FROM\n" +
            "(SELECT devSn,MAX(id) AS id FROM bingtiao_table GROUP BY devSn ) a\n" +
            "LEFT JOIN\n" +
            "(SELECT id,stateCode FROM bingtiao_table WHERE DATEDIFF(time,now())=0) b\n" +
            "ON a.id=b.id GROUP BY devSn ) a\n" +
            "WHERE a.stateCode='1'\n" +
            "UNION\n" +
            "SELECT '抓棉机' AS gongxu,COUNT(*) AS sjyx  FROM (\n" +
            "SELECT a.devSn,b.stateCode FROM\n" +
            "(SELECT devSn,MAX(id) AS id FROM zmj_table GROUP BY devSn ) a\n" +
            "LEFT JOIN\n" +
            "(SELECT id,stateCode FROM zmj_table WHERE DATEDIFF(time,now())=0) b\n" +
            "ON a.id=b.id GROUP BY devSn ) a\n" +
            "WHERE a.stateCode='1'\n" +
            "UNION\n" +
            "SELECT '多仓' AS gongxu,COUNT(*) AS sjyx  FROM (\n" +
            "SELECT a.devSn,b.stateCode FROM\n" +
            "(SELECT devSn,MAX(id) AS id FROM duocan_table GROUP BY devSn ) a\n" +
            "LEFT JOIN\n" +
            "(SELECT id,stateCode FROM duocan_table WHERE DATEDIFF(time,now())=0) b\n" +
            "ON a.id=b.id GROUP BY devSn ) a\n" +
            "WHERE a.stateCode='1'\n" +
            "UNION\n" +
            "SELECT '络筒' AS gongxu,COUNT(*) AS sjyx  FROM (\n" +
            "SELECT a.devSn,b.stateCode FROM\n" +
            "(SELECT devSn,MAX(id) AS id FROM spero_table GROUP BY devSn ) a\n" +
            "LEFT JOIN\n" +
            "(SELECT id,stateCode FROM spero_table WHERE DATEDIFF(time,now())=0) b\n" +
            "ON a.id=b.id GROUP BY devSn ) a\n" +
            "WHERE a.stateCode='1'\n" +
            "UNION\n" +
            "SELECT '梳棉' AS gongxu,COUNT(*) AS sjyx  FROM (\n" +
            "SELECT a.devSn,b.stateCode FROM\n" +
            "(SELECT devSn,MAX(id) AS id FROM shumianji_table GROUP BY devSn ) a\n" +
            "LEFT JOIN\n" +
            "(SELECT id,stateCode FROM shumianji_table WHERE DATEDIFF(time,now())=0) b\n" +
            "ON a.id=b.id GROUP BY devSn \n" +
            "UNION\n" +
            "SELECT a.devSn,b.stateCode FROM\n" +
            "(SELECT devSn,MAX(id) AS id FROM shumianjia_table GROUP BY devSn ) a\n" +
            "LEFT JOIN\n" +
            "(SELECT id,stateCode FROM shumianjia_table WHERE DATEDIFF(time,now())=0) b\n" +
            "ON a.id=b.id GROUP BY devSn \n" +
            ") a\n" +
            "WHERE a.stateCode='1'")
    List<SjYx> workshopOpeningRatioBarChartSjYx();

    /**
     * 细纱车台运行状态统计
     *
     * @return
     */
    List<RunningState> runningState();

    /**
     * 自络各品种产量
     */
    List<YieldOfEachVariety> querySpoolingYieldVariety(SchedulingShiftInModel params);

    /**
     * 当班各工序产量-络筒
     *
     * @return
     */
    EachProcedureYield eachProcedureYieldSpooling(SchedulingShiftInModel params);

    /**
     * 当班各工序产量-细纱
     *
     * @return
     */
    EachProcedureYield eachProcedureYieldXiSha(SchedulingShiftInModel params);

    /**
     * 当班各工序产量-粗纱新旧合并
     */
    EachProcedureYield eachProcedureYieldCuSha(SchedulingShiftInModel params);

    /**
     * 当班各工序产量-并条-末并
     *
     * @return
     */
    EachProcedureYield eachProcedureYieldBingTiao(SchedulingShiftInModel params);

    /**
     * 当班各工序产量-梳棉
     *
     * @return
     */
    EachProcedureYield eachProcedureYieldShuMian(SchedulingShiftInModel params);

    // 查询温度
    @Select("<script>" +
            "SELECT * FROM \n" +
            "(SELECT a.*,ROW_NUMBER() OVER(PARTITION BY a.ftime ORDER BY time DESC) AS Row_Sort FROM\n" +
            "(SELECT *, DATE_FORMAT(time, '%Y-%m-%d %H:00:00') AS ftime FROM wenshidu_table) a) a\n" +
            "WHERE  a.Row_Sort=1" +
            "<choose>" +
            "<when test='isNow'>" +
            "ORDER BY id DESC LIMIT 0,1" +
            "</when> " +
            "<otherwise>" +
            "<if test='startTime != null'>" +
            "  and time>=#{startTime} " +
            "</if>" +
            "<if test='endTime != null'>" +
            " and #{endTime}>=time" +
            "</if>" +
            "  </otherwise> " +
            "</choose>" +
            "</script>")
    List<WenshiduTable> queryWenShiDu(TimeReq timeReq);

    /**
     * 查询各工序当前耗电量-ImpEP
     *
     * @return
     */
    @Select("SELECT SUM(a.ImpEp) AS ImpEp,a.gongxu FROM\n" +
            "(SELECT gongxu,devSn,MAX(ImpEp)-MIN(ImpEp) AS ImpEp FROM dian_table \n" +
            "WHERE DATEDIFF(time,date(now()))=0  GROUP BY devSn,gongxu) a GROUP BY gongxu")
    List<ImpEpSum> queryImpEp();

    @Select("SELECT yali\n" +
            "FROM yaliji_table\n" +
            "where devSn = #{devSn} and time BETWEEN str_to_date(#{pointInTime}, '%Y%m%d%H%i') and date_add(str_to_date(#{pointInTime}, '%Y%m%d%H%i'),\n" +
            "                                                                          interval 29 minute)\n" +
            "limit 1")
    String getYlByPointInTime(@Param("pointInTime") String pointInTime, @Param("devSn") String devSn);

    @Select("SELECT yali FROM yaliji_table\n" +
            "where devSn = #{devSn}\n" +
            "and time BETWEEN date_sub(str_to_date(#{pointInTime}, '%Y%m%d%H%i'), interval 5 minute)\n" +
            "and date_add(str_to_date(#{pointInTime}, '%Y%m%d%H%i'), interval 5 minute) limit 1")
    String getYlByLatelyTime(@Param("pointInTime") String pointInTime, @Param("devSn") String devSn);

    @Select("SELECT Iavg, devSn, time dateTime FROM dian_table where gongxu ='空压' and devSn = #{devSn} ORDER BY time desc")
    List<DianLiu> getDlByDevSnReq(DlByDevSnReq devSn);

    @Select("SELECT Iavg, devSn, time dateTime, impEp FROM dian_table where gongxu ='空压' and devSn = #{devSn} ORDER BY time desc limit 1")
    DianLiu getLatelyDlByDevSnReq(@Param("devSn") String devSn);

    @Select("select devSn, count(*) cs from dian_table where gongxu ='空压' group by devSn having cs > 0")
    List<DlByDevSnReq> getAllDevSn();

    @Select("select count(*) cs from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mes_database' and TABLE_NAME=#{tableName}")
    Integer isHaveDataByXs(@Param("tableName") String tableName);

    XsHistory getHistoryTrackData(DateSectionQueryReq dateSectionReq);

    // @Select("select devSn, date_format(time, '%Y-%m-%d %H:00') time, max(ImpEp) impEp from dian_table where gongxu = #{gongXu} and time >= #{startTime} and time <= #{endTime} group by devSn, date_format(time, '%Y-%m-%d %H:00')")
    List<DianLiRes> queryImpEpByTime(DianHaoReq timeReq);


    /**
     * 获取折线图数据-2
     *
     * @param devSn
     * @return
     */
    @Select("SELECT a.devSn,a.llxs,b.Iavg,ROUND((a.llxs/b.Iavg),3) AS divisor,a.time,b.time FROM\n" +
            "(\n" +
            "SELECT devSn,llxs,time,date_format(time,'%y-%m-%d %H:%i') AS f_time\n" +
            "FROM xsmzl_table WHERE DATEDIFF(time,now())=0 AND devSn=#{devSn} GROUP BY f_time\n" +
            ") a\n" +
            "LEFT JOIN \n" +
            "(\n" +
            "SELECT devSn,Iavg,time,date_format(time,'%y-%m-%d %H:%i') AS f_time\n" +
            "FROM dian_table WHERE gongxu='细纱' AND DATEDIFF(time,now())=0 AND devSn=#{devSn} GROUP BY f_time\n" +
            ") b\n" +
            "ON a.f_time=b.f_time HAVING b.time IS NOT NULL")
    List<LineChartForA> getLineChartData2(String devSn);

    /**
     * 细纱1.5级用工数据
     *
     * @return
     */
    @Select("SELECT a.devSn,a.pz,a.xiaolv,a.jhcl,a.cl,a.gh, a.name AS dcg,a.gs,ROUND((a.s_zcd*b.lzxs*a.share)/100,2) AS gz FROM\n" +
            "(\n" +
            "SELECT a.*,ROUND(((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),a.start_time)))/(24*60*60)*a.tc*(a.xiaolv/100), 3) AS jhcl,ROUND((a.s_zcd*a.dl*a.ds)/100000, 3) AS cl FROM\n" +
            "(\n" +
            "SELECT a.*,b.ds,b.dl,b.tc,b.zs,b.pz FROM\n" +
            "(\n" +
            "SELECT a.*,b.xiaolv FROM\n" +
            "(\n" +
            "SELECT a.*,MAX(b.zcd+0)-MIN(b.zcd+0) AS s_zcd, MAX(b.time) AS time FROM\n" +
            "(SELECT a.gh,b.name,a.devSn,a.`share`,a.start_time,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),a.start_time))/60 AS gs,a.work_type\n" +
            "FROM\n" +
            "(SELECT current_work_type work_type,gh,devSn,`share`,start_time from scheduling where current_gongxu='细纱' AND current_work_type='挡车工' AND now()>=start_time AND now()<=end_time ORDER BY devSn+0 ASC) a\n" +
            "LEFT JOIN\n" +
            "(SELECT gh,`name`,work_type FROM scheduling_personnel ) b\n" +
            "ON a.gh=b.gh) a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,time,zcd FROM xsmzl_table WHERE DATEDIFF(time,date(now()))=0 ) b\t\t\t\t\t\n" +
            "ON a.devSn=b.devSn AND a.start_time<=b.time\n" +
            "GROUP BY a.devSn,a.name\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,convert((runTime/totalTime)*100,decimal(10,2)) AS xiaolv,time FROM xsmzl_table WHERE DATEDIFF(time,date(now()))=0) b\n" +
            "ON a.devSn=b.devSn AND a.time=b.time\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,ds,dl,tc,pz,zs FROM mes_newscjh WHERE gongxu='细纱' AND DATEDIFF(time,now())=0) b\n" +
            "ON a.devSn=b.devSn\n" +
            ") a\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT work_type,zs,lzxs FROM scheduling_labor_capital WHERE gongxu='细纱'\n" +
            ") b\n" +
            "ON a.work_type=b.work_type AND a.zs=b.zs \n" +
            "ORDER BY devSn+0 ASC")
    List<UseWorkersDetails> queryUseWorkersDetailsNight();

    /**
     * 细纱1.5级用工数据-晚班-汇总
     *
     * @return
     */
    @Select("SELECT a.gh,a.dcg,a.pz,COUNT(a.devSn) AS num_devSn,AVG(a.xiaolv) AS avg_xiaolv,SUM(a.jhcl) AS sum_jhcl,SUM(a.cl) sum_cl,a.gs,SUM(a.gz) sum_gz FROM (" +
            "SELECT a.devSn,a.pz,a.xiaolv,a.jhcl,a.cl,a.gh, a.name AS dcg,a.gs,ROUND((a.s_zcd*b.lzxs*a.share)/100,2) AS gz FROM\n" +
            "(\n" +
            "SELECT a.*,ROUND(((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),a.start_time)))/(24*60*60)*a.tc*(a.xiaolv/100), 3) AS jhcl,ROUND((a.s_zcd*a.dl*a.ds)/100000, 3) AS cl FROM\n" +
            "(\n" +
            "SELECT a.*,b.ds,b.dl,b.tc,b.zs,b.pz FROM\n" +
            "(\n" +
            "SELECT a.*,b.xiaolv FROM\n" +
            "(\n" +
            "SELECT a.*,MAX(b.zcd+0)-MIN(b.zcd+0) AS s_zcd, MAX(b.time) AS time FROM\n" +
            "(SELECT a.gh,b.name,a.devSn,a.`share`,a.start_time,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),a.start_time))/60 AS gs,a.work_type\n" +
            "FROM\n" +
            "(SELECT current_work_type work_type,gh,devSn,`share`,start_time from scheduling where current_gongxu='细纱' AND current_work_type='挡车工' AND now()>=start_time AND now()<=end_time ORDER BY devSn+0 ASC) a\n" +
            "LEFT JOIN\n" +
            "(SELECT gh,`name` FROM scheduling_personnel) b\n" +
            "ON a.gh=b.gh) a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,time,zcd FROM xsmzl_table WHERE DATEDIFF(time,date(now()))=0 ) b\t\t\t\t\t\n" +
            "ON a.devSn=b.devSn AND a.start_time<=b.time\n" +
            "GROUP BY a.devSn,a.name\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,convert((runTime/totalTime)*100,decimal(10,2)) AS xiaolv,time FROM xsmzl_table WHERE DATEDIFF(time,date(now()))=0) b\n" +
            "ON a.devSn=b.devSn AND a.time=b.time\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,ds,dl,tc,pz,zs FROM mes_newscjh WHERE gongxu='细纱' AND DATEDIFF(time,now())=0) b\n" +
            "ON a.devSn=b.devSn\n" +
            ") a\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT work_type,zs,lzxs FROM scheduling_labor_capital WHERE gongxu='细纱'\n" +
            ") b\n" +
            "ON a.work_type=b.work_type AND a.zs=b.zs \n" +
            ") a GROUP BY a.dcg,a.pz " +
            "ORDER BY devSn+0 ASC")
    List<UseWorkersDetailsSum> queryUseWorkersDetailsNightSummary();

    /**
     * 细纱1.5级用工数据-设备区间
     *
     * @param name
     * @param pz
     * @return
     */
    @Select("SELECT * FROM (\n" +
            "SELECT a.devSn,a.name,b.pz FROM\n" +
            "(SELECT b.name,a.devSn\n" +
            "FROM\n" +
            "(SELECT gh,devSn from scheduling where current_gongxu=#{gongxu} AND current_work_type='挡车工' AND now()>=start_time AND now()<=end_time ) a\n" +
            "LEFT JOIN\n" +
            "(SELECT DISTINCT gh,`name` FROM scheduling_personnel) b\n" +
            "ON a.gh=b.gh ORDER BY name ASC,devSn ASC) a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,pz FROM mes_newscjh WHERE gongxu=#{gongxu} AND DATEDIFF(time,now())=0) b\n" +
            "ON\n" +
            "a.devSn=b.devSn\n" +
            ") a\n" +
            "WHERE a.`name`=#{name} AND a.pz=#{pz}\n" +
            "ORDER BY a.devSn+0 ASC")
    List<DevSnAreaList> queryUseWorkersDetailsEquipmentSection(String gongxu, String name, String pz);

    /**
     * 粗纱1.5级明细数据
     *
     * @return
     */
    @Select("SELECT a.devSn,a.pz,a.xiaolv,a.dqsjcl dqcl,a.dqllcl dqjhcl,ROUND(a.tc/2*(a.xiaolv/100), 3) AS bjhcl,a.ds,ROUND((a.dqsjcl/a.dqllcl)*100,3) AS wcl,b.dh dbdh,ROUND((b.dh*10000)/a.zcd,3) AS wmdh,b.Uavg,b.Iavg,ROUND(a.llxs,2) llxs,ROUND((b.dh*1000)/a.dqsjcl,2) AS dsdh FROM\n" +
            "(SELECT a.devSn,ROUND((a.zcd*b.dl)/100,3) dqsjcl,ROUND(IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*b.tc*(b.xiaolv/100),((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*b.tc*(b.xiaolv/100)),3) AS dqllcl,b.tc,a.xiaolv*100 xiaolv,pz,a.ds,a.zcd,a.llxs FROM\n" +
            "(\n" +
            "-- 旧粗纱\n" +
            "SELECT a.*,b.llxs,b.dyzs ds,b.xiaolv FROM\n" +
            "(SELECT MAX(id) id,devSn,MAX(zcl+0)-MIN(zcl+0) zcd FROM txcsoml_table WHERE DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn) a\n" +
            "LEFT JOIN\n" +
            "(SELECT id,conv(llxs,10,16) llxs,dyzs,ROUND(runTime/totalTime,2) xiaolv FROM txcsoml_table) b\n" +
            "ON a.id=b.id\n" +
            "UNION\n" +
            "-- 新粗纱\n" +
            "SELECT a.*,b.llxs,b.dysjzs ds,b.xiaolv FROM\n" +
            "(SELECT MAX(id) id,devSn,MAX(ljsc+0)-MIN(ljsc+0) zcd FROM txcsomlnew_table WHERE DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn) a\n" +
            "LEFT JOIN\n" +
            "(SELECT id,llxsd llxs,dysjzs,ROUND(runTime/totalTime,2) xiaolv FROM txcsomlnew_table) b\n" +
            "ON a.id=b.id\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,dl,ds,tc,xiaolv,pz FROM mes_newscjh WHERE DATEDIFF(time,now())=0 AND gongxu='粗纱' GROUP BY devSn) b\n" +
            "ON\n" +
            "a.devSn=b.devSn) a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT a.*,b.Uavg,b.Iavg FROM (\n" +
            "-- 粗纱电量\n" +
            "SELECT MAX(id) id,devSn,MAX(ImpEp)-MIN(ImpEp) AS dh FROM dian_table\n" +
            "WHERE gongxu='粗纱' AND DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn\n" +
            ") a LEFT JOIN\n" +
            "(SELECT id,Uavg,Iavg FROM dian_table) b\n" +
            "ON a.id=b.id\n" +
            ") b\n" +
            "ON a.devSn=b.devSn ORDER BY a.devSn+0")
    List<DefiniteAndDetailed> queryDefiniteAndDetailedCs();

    /**
     * 梳棉1.5级明细数据
     *
     * @return
     */
    @Select("SELECT a.devSn,a.pz,a.xiaolv,a.dqjhcl,a.dqcl,a.bjhcl,a.ctsd,a.ds,ROUND((a.dqcl/a.bjhcl)*100,3) AS wcl,a.dh dbdh,ROUND((a.dh*10000)/a.a_zcd,3) AS wmdh,a.Uavg,a.Iavg FROM (\n" +
            "SELECT a.devSn,a.pz,a.xiaolv,IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*a.tc,((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*a.tc) AS dqjhcl,ROUND((b.a_zcd*a.dl)/1000, 3) AS dqcl,ROUND(a.tc/2, 3) AS bjhcl,a.ds,a.dh,a.tc,a.gongxu,a.dl,b.a_zcd,b.ctsd,a.Uavg,a.Iavg\n" +
            "FROM\n" +
            "(\n" +
            "SELECT a.*,b.dh,b.Uavg,b.Iavg FROM (\n" +
            "SELECT devSn,pz,gongxu,ds,dl,tc*(xiaolv/100) tc,xiaolv FROM mes_newscjh WHERE gongxu='梳棉' AND DATEDIFF(now(),time)=0) a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT a.*,b.Uavg,b.Iavg FROM \n" +
            "(SELECT MAX(id) id,devSn,MAX(ImpEp)-MIN(ImpEp) AS dh FROM dian_table WHERE gongxu='梳棉' AND DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn) a\n" +
            "LEFT JOIN\n" +
            "(SELECT id,Uavg,Iavg FROM dian_table) b\n" +
            "ON a.id=b.id\n" +
            ") b \n" +
            "ON a.devSn=b.devSn\n" +
            ")a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT a.*,b.ctsd FROM (\n" +
            "SELECT MAX(id) id,devSn,MAX(zcd+0.0)-MIN(zcd+0.0) AS a_zcd FROM shumianji_table\n" +
            "WHERE DATEDIFF(time,date(now()))=0 AND (zcd REGEXP '[^0-9.]')=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn) a\n" +
            "LEFT JOIN \n" +
            "(SELECT id,ctsd FROM shumianji_table) b\n" +
            "ON a.id=b.id\n" +
            "UNION\n" +
            "SELECT a.*,b.ctsd FROM (\n" +
            "SELECT MAX(id) id,devSn,MAX(zcd+0.0)-MIN(zcd+0.0) AS a_zcd FROM shumianjia_table\n" +
            "WHERE DATEDIFF(time,date(now()))=0 AND (zcd REGEXP '[^0-9.]')=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn) a\n" +
            "LEFT JOIN \n" +
            "(SELECT id,ctsd FROM shumianjia_table) b\n" +
            "ON a.id=b.id\n" +
            ") b\n" +
            "ON a.devSn=b.devSn\n" +
            ") a ORDER BY a.devSn+0 ASC")
    List<DefiniteAndDetailed> queryDefiniteAndDetailedSm();

    /**
     * 并条1.5级明细数据
     *
     * @return
     */
    @Select("SELECT a.devSn,a.pz,a.xiaolv,ROUND(a.dqjhcl,3) dqjhcl,ROUND(a.dqcl,3) dqcl,a.bjhcl,a.ctsd,a.ds,ROUND((a.dqcl/a.bjhcl)*100,3) AS wcl,b.dh dbdh,ROUND((b.dh*10000)/a.a_zcd,3) AS wmdh,b.Uavg,b.Iavg  FROM \n" +
            "(SELECT b.devSn,b.pz,b.xiaolv,IF(HOUR(now())<12,(TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())/(24*60*60))*b.tc,((TIMESTAMPDIFF(SECOND,DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s'),INTERVAL 0 DAY),now())-(12*60*60))/(24*60*60))*b.tc)  dqjhcl,(a.a_zcd*b.dl*b.ds)/1000 AS dqcl,ROUND(b.tc/2, 3) AS bjhcl,b.ds,a.ctsd,a.a_zcd FROM\n" +
            "(\n" +
            "SELECT a.*,b.ctsd FROM \n" +
            "(\n" +
            "SELECT MAX(id) id,devSn,MAX(zcd+0.0)-MIN(zcd+0.0) AS a_zcd FROM bingtiao_table\n" +
            "WHERE devSn>100 AND DATEDIFF(time,date(now()))=0 AND (zcd REGEXP '[^0-9.]')=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn ORDER BY devSn+0 ASC\n" +
            ") a LEFT JOIN\n" +
            "(SELECT id,ctsd FROM bingtiao_table) b ON a.id=b.id\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT gongxu,devSn,dl,ds,pz,tc*(xiaolv/100) tc,xiaolv FROM mes_newscjh WHERE DATEDIFF(time,now())=0 AND gongxu='并条') b\n" +
            "ON a.devSn=b.devSn) a\n" +
            "LEFT JOIN\n" +
            "(SELECT a.devSn,a.dh,b.Uavg,b.Iavg FROM\n" +
            "(SELECT a.devSn,SUM(a.dh) dh ,MAX(a.id) id FROM (\n" +
            "SELECT IF(devSn<100,devSn+100,devSn-900) devSn,MAX(ImpEp)-MIN(ImpEp) AS dh,IF(devSn<100,'',MAX(id)) id FROM dian_table\n" +
            "WHERE gongxu='并条' AND DATEDIFF(time,now())=0 AND IF(HOUR(now())<12,extract(hour from time)<12,extract(hour from time)>=12) GROUP BY devSn ) a GROUP BY devSn) a\n" +
            "LEFT JOIN\n" +
            "(SELECT id,Uavg,Iavg FROM dian_table) b\n" +
            "ON a.id=b.id) b\n" +
            "ON a.devSn=b.devSn")
    List<DefiniteAndDetailed> queryDefiniteAndDetailedBt();

    /**
     * 络筒1.5级明细数据
     *
     * @return
     */
    List<DefiniteAndDetailed> queryDefiniteAndDetailedLt();

    /**
     * 细纱1.5级明细数据-早班
     *
     * @return
     */
    List<DefiniteAndDetailed> queryXiShaDefiniteAndDetailed(SchedulingShiftInModel params);

    /**
     * 获取落纱工工资
     *
     * @param data
     * @return
     */
    @Select("SELECT a.gh,a.name,((b.xdgz/b.num)+a.qjgz) AS gz FROM\n" +
            "(\n" +
            "SELECT a.gh,a.name,SUM(a.share*b.lzxs) AS qjgz,a.current_dh FROM\n" +
            "(SELECT a.gh,a.name,a.share,a.current_dh,b.zs FROM\n" +
            "(\n" +
            "SELECT a.gh,b.name,a.devSn,a.`share`,a.current_dh\n" +
            "FROM\n" +
            "(SELECT gh,devSn,`share`,current_dh from scheduling where current_gongxu='细纱' AND current_work_type='落纱队员' AND DATEDIFF(start_time,#{time})=0 AND bc=#{bc}) a\n" +
            "LEFT JOIN\n" +
            "(SELECT gh,`name` FROM scheduling_personnel WHERE work_type='落纱队员') b\n" +
            "ON a.gh=b.gh\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,zs FROM mes_newscjh WHERE gongxu='细纱' AND DATEDIFF(time,now())=0) b\n" +
            "ON a.devSn=b.devSn) a\n" +
            "LEFT JOIN\n" +
            "(SELECT * FROM scheduling_labor_capital WHERE gongxu='细纱' AND work_type=\"落纱清洁\") b\n" +
            "ON\n" +
            "a.zs=b.zs\n" +
            "GROUP BY gh\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT a.current_dh,SUM(a.grgz) AS xdgz,COUNT(*)+1 AS num FROM ( \n" +
            "SELECT a.current_dh,SUM(a.agz) AS grgz,a.gh,a.name FROM ( " +
            "SELECT a.gh,a.name,a.devSn,a.current_dh,(a.clgz+b.lsgz+b.hsgz) AS agz FROM\n" +
            "(\n" +
            "SELECT a.gh,a.name,a.devSn,a.current_dh,ROUND((a.s_zcd*b.lzxs*a.share)/100,2) AS clgz FROM (\n" +
            "SELECT a.gh,a.name,a.devSn,a.`share`,a.current_dh,a.s_zcd,b.zs FROM (\n" +
            "SELECT a.gh,a.name,a.devSn,a.`share`,a.current_dh,MAX(b.zcd+0)-MIN(b.zcd+0) AS s_zcd FROM (\n" +
            "SELECT a.gh,b.name,a.devSn,a.`share`,a.end_time,a.current_dh\n" +
            "FROM\n" +
            "(SELECT gh,devSn,`share`,end_time,current_dh from scheduling where current_gongxu='细纱' AND current_work_type='落纱队员' AND DATEDIFF(start_time,#{time})=0 AND bc=#{bc}) a\n" +
            "LEFT JOIN\n" +
            "(SELECT gh,`name` FROM scheduling_personnel WHERE work_type='落纱队员') b\n" +
            "ON a.gh=b.gh\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,time,zcd FROM xsmzl_table WHERE DATEDIFF(time,#{time})=0 ) b\n" +
            "ON a.devSn=b.devSn AND a.end_time>=b.time\n" +
            "GROUP BY a.devSn,a.name\n" +
            " ) a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,zs FROM mes_newscjh WHERE gongxu='细纱' AND DATEDIFF(time,#{time})=0) b\n" +
            "ON a.devSn=b.devSn\t\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT work_type,zs,lzxs FROM scheduling_labor_capital WHERE gongxu='细纱' AND work_type='落纱产量') b\n" +
            "ON a.zs=b.zs \n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT a.devSn,a.lsgz,b.hsgz FROM\n" +
            "(\n" +
            "SELECT a.devSn,a.ls*b.lzxs AS lsgz FROM \n" +
            "(SELECT devSn,ls,time,gongxu FROM device_table WHERE gongxu='细纱' AND DATEDIFF(time,#{time})=0 AND bc=#{bc}) a \n" +
            "LEFT JOIN \n" +
            "(SELECT * FROM scheduling_labor_capital WHERE gongxu='细纱' AND work_type=\"落纱系数\") b \n" +
            "ON a.gongxu=b.gongxu\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT a.devSn,a.input*b.lzxs AS hsgz FROM \n" +
            "(SELECT devSn,input,time,gongxu FROM device_table WHERE gongxu='细纱' AND DATEDIFF(time,#{time})=0 AND bc=#{bc}) a \n" +
            "LEFT JOIN \n" +
            "(SELECT * FROM scheduling_labor_capital WHERE gongxu='细纱' AND work_type=\"换纱系数\") b \n" +
            "ON a.gongxu=b.gongxu\n" +
            ") b\n" +
            "ON a.devSn=b.devSn\n" +
            ") b\n" +
            "ON a.devSn=b.devSn\n" +
            ") a\n" +
            "GROUP BY a.gh\n" +
            ") a\n" +
            "GROUP BY a.current_dh " +
            ") b\n" +
            "ON a.current_dh=b.current_dh")
    List<LsSalary> getDailySalaryOfLs(DailySalary data);

    /**
     * 获取落纱工队长工资
     *
     * @param data
     * @return
     */
    @Select("SELECT a.gh,a.`name`,b.gz FROM\n" +
            "(SELECT a.gh,b.name,a.current_dh\n" +
            "FROM\n" +
            "(SELECT gh,current_dh from scheduling where current_gongxu='细纱' AND current_work_type='落纱队长' AND DATEDIFF(start_time,#{time})=0 AND bc=#{bc} GROUP BY current_dh\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT gh,`name` FROM scheduling_personnel WHERE work_type='落纱队员') b\n" +
            "ON a.gh=b.gh) a\n" +
            "LEFT JOIN\n" +
            "(SELECT a.current_dh,((a.qjgz_sum+xdgz)/b.num)+6 AS gz FROM (\n" +
            "SELECT SUM(qjgz) qjgz_sum,current_dh FROM (\n" +
            "SELECT a.gh,a.name,SUM(a.share*b.lzxs) AS qjgz,a.current_dh FROM\n" +
            "(SELECT a.gh,a.name,a.share,a.current_dh,b.zs FROM\n" +
            "(\n" +
            "SELECT a.gh,b.name,a.devSn,a.`share`,a.current_dh\n" +
            "FROM\n" +
            "(SELECT gh,devSn,`share`,current_dh from scheduling where current_gongxu='细纱' AND current_work_type='落纱队员' AND DATEDIFF(start_time,#{time})=0 AND bc=#{bc}) a\n" +
            "LEFT JOIN\n" +
            "(SELECT gh,`name` FROM  scheduling_personnel WHERE work_type='落纱队员') b\n" +
            "ON a.gh=b.gh\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,zs FROM mes_newscjh WHERE gongxu='细纱' AND DATEDIFF(time,now())=0) b\n" +
            "ON a.devSn=b.devSn) a\n" +
            "LEFT JOIN\n" +
            "(SELECT * FROM scheduling_labor_capital WHERE gongxu='细纱' AND work_type=\"落纱清洁\") b\n" +
            "ON\n" +
            "a.zs=b.zs\n" +
            "GROUP BY gh\n" +
            ") a GROUP BY current_dh\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT a.current_dh,SUM(a.grgz) AS xdgz,COUNT(*)+1 AS num FROM ( \n" +
            "SELECT a.current_dh,SUM(a.agz) AS grgz,a.gh,a.name FROM ( \n" +
            "SELECT a.gh,a.name,a.devSn,a.current_dh,(a.clgz+b.lsgz+b.hsgz) AS agz FROM\n" +
            "(\n" +
            "SELECT a.gh,a.name,a.devSn,a.current_dh,ROUND((a.s_zcd*b.lzxs*a.share)/100,2) AS clgz FROM (\n" +
            "SELECT a.gh,a.name,a.devSn,a.`share`,a.current_dh,a.s_zcd,b.zs FROM (\n" +
            "SELECT a.gh,a.name,a.devSn,a.`share`,a.current_dh,MAX(b.zcd+0)-MIN(b.zcd+0) AS s_zcd FROM (\n" +
            "SELECT a.gh,b.name,a.devSn,a.`share`,a.end_time,a.current_dh\n" +
            "FROM\n" +
            "(SELECT gh,devSn,`share`,end_time,current_dh from scheduling where current_gongxu='细纱' AND current_work_type='落纱队员' AND DATEDIFF(start_time,#{time})=0 AND bc=#{bc}\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT gh,`name` FROM scheduling_personnel WHERE work_type='落纱队员') b\n" +
            "ON a.gh=b.gh\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,time,zcd FROM xsmzl_table WHERE DATEDIFF(time,#{time})=0) b\n" +
            "ON a.devSn=b.devSn AND a.end_time>=b.time\n" +
            "GROUP BY a.devSn,a.name\t\t\t\n" +
            " ) a\n" +
            "LEFT JOIN\n" +
            "(SELECT devSn,zs FROM mes_newscjh WHERE gongxu='细纱' AND DATEDIFF(time,#{time})=0\n" +
            ") b\n" +
            "ON a.devSn=b.devSn\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(SELECT work_type,zs,lzxs FROM scheduling_labor_capital WHERE gongxu='细纱' AND work_type='落纱产量') b\n" +
            "ON a.zs=b.zs \n" +
            "ORDER BY a.devSn+0 ASC\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT a.devSn,a.lsgz,b.hsgz FROM\n" +
            "(\n" +
            "SELECT a.devSn,a.ls*b.lzxs AS lsgz FROM \n" +
            "(SELECT devSn,ls,time,gongxu FROM device_table WHERE gongxu='细纱' AND DATEDIFF(time,#{time})=0 AND bc=#{bc}) a \n" +
            "LEFT JOIN \n" +
            "(SELECT * FROM scheduling_labor_capital WHERE gongxu='细纱' AND work_type=\"落纱系数\") b \n" +
            "ON a.gongxu=b.gongxu\n" +
            ") a\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "SELECT a.devSn,a.input*b.lzxs AS hsgz FROM \n" +
            "(SELECT devSn,input,time,gongxu FROM device_table WHERE gongxu='细纱' AND DATEDIFF(time,#{time})=0 AND bc=#{bc}) a \n" +
            "LEFT JOIN \n" +
            "(SELECT * FROM scheduling_labor_capital WHERE gongxu='细纱' AND work_type=\"换纱系数\") b \n" +
            "ON a.gongxu=b.gongxu\n" +
            ") b\n" +
            "ON a.devSn=b.devSn\n" +
            ") b\n" +
            "ON a.devSn=b.devSn ORDER BY a.devSn+0 ASC\n" +
            ") a\n" +
            "GROUP BY a.gh\n" +
            ") a\n" +
            "GROUP BY a.current_dh\n" +
            ") b\n" +
            "ON a.current_dh=b.current_dh) b\n" +
            "ON a.current_dh=b.current_dh")
    List<LsSalary> getDailySalaryOfLsCaptain(DailySalary data);

}
